package com.embrion.db;

import java.io.File;


import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Properties;

import com.embrion.bean.Alunno;
import com.embrion.bean.Classe;
import com.embrion.bean.Professore;
import com.embrion.bean.Scuola;
import com.embrion.utils.Const;

public class DataHandler {

	private static ConnectionPool cp = null;

	private static String settingsFilePath = null;
	private static String driver = "";
	private static String dbAddress = "";
	private static String db = "";
	private static String user = "";
	private static String password = "";
	private static String ip = "";

	public DataHandler() {
		new DataHandler(Const.homePath + Const.dbSettingsPath);
	}

	public DataHandler(String _settingsFilePath) {

		if (DataHandler.cp == null && _settingsFilePath != null) {
			DataHandler.settingsFilePath = _settingsFilePath;
			setup();
		}
	}

	private void setup() {

		if (DataHandler.cp != null) {
			return;
		}

		try {
			File file = new File(DataHandler.settingsFilePath);
			FileInputStream fis = new FileInputStream(file);
			Properties prop = new Properties();
			prop.loadFromXML(fis);

			DataHandler.driver = prop.getProperty("driver");
			DataHandler.user = prop.getProperty("user");
			DataHandler.password = prop.getProperty("password");
			DataHandler.ip = prop.getProperty("ip");
			DataHandler.dbAddress = prop.getProperty("address");
			DataHandler.db = prop.getProperty("database");
			System.out.println(driver + " " + user + " " + password + " " + ip
					+ " " + dbAddress + db);
		} catch (Exception e) {
			e.printStackTrace();
		}

		try {

			Class.forName(driver).newInstance();
			cp = new ConnectionPool(DataHandler.dbAddress + DataHandler.ip
					+ DataHandler.db + "?user=" + DataHandler.user
					+ "&password=" + DataHandler.password, DataHandler.driver,
					1, 1);
			System.out.println("\nPool di connessioni al DATABASE: \""
					+ DataHandler.db + "\" creato con successo!\n");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * Chiude il pool di connessioni
	 */
	public void destroy() {
		if (cp != null) {
			cp.destroyConnections();
			cp = null;
		}
	}


	//Restituisce gli utenti del sistema
	public Professore getUser(String un, String pw) {

		Professore p = null;
	
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet res = null;

		try {
			conn = (Connection) cp.getConnection();
			stat = (PreparedStatement) conn.prepareStatement(QueryConstant.GET_PROFESSORE);
			stat.setString(1, un);
			stat.setString(2, pw);

			res = (ResultSet) stat.executeQuery();

			if (res.next()) {

				p = new Professore(res);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (res != null) {
				try {
					res.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				res = null;
			}
			if (stat != null) {
				try {
					stat.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				stat = null;
			}
			if (conn != null) {
				cp.returnConnection(conn);
				conn = null;
			}
		}

		return p;
		
		
	}

	
	//TODO: Debolezza, 1 professore per una scuola
	
	public ArrayList<Scuola> getScuola(Professore p) {

		ArrayList<Scuola> scuolaAL = null;
	
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet res = null;

		try {
			conn = (Connection) cp.getConnection();
			stat = (PreparedStatement) conn.prepareStatement(QueryConstant.SELECT_SCUOLA_DEL_PROFESSORE);
			stat.setInt(1, p.getId_scuola());
		

			res = (ResultSet) stat.executeQuery();

			int i = 0;
			while (res.next()) {
				if(i==0){
					scuolaAL=new ArrayList<Scuola>();
					i++;
				}
				
				scuolaAL.add(new Scuola(res));
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (res != null) {
				try {
					res.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				res = null;
			}
			if (stat != null) {
				try {
					stat.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				stat = null;
			}
			if (conn != null) {
				cp.returnConnection(conn);
				conn = null;
			}
		}

		return scuolaAL;
		
		
	}

	public ArrayList<Classe> getClasse(Integer id_scuola) {

		ArrayList<Classe> classeAL = null;
	
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet res = null;

		try {
			conn = (Connection) cp.getConnection();
			stat = (PreparedStatement) conn.prepareStatement(QueryConstant.SELECT_CLASSI_DELLA_SCUOLA);
			stat.setInt(1, id_scuola);
		

			res = (ResultSet) stat.executeQuery();

			int i = 0;
			while (res.next()) {
				if(i==0){
					classeAL=new ArrayList<Classe>();
					i++;
				}
				
				classeAL.add(new Classe(res));
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (res != null) {
				try {
					res.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				res = null;
			}
			if (stat != null) {
				try {
					stat.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				stat = null;
			}
			if (conn != null) {
				cp.returnConnection(conn);
				conn = null;
			}
		}

		return classeAL;
		
		
	}

	public ArrayList<Alunno> getAlunni(Integer id_classe) {

		ArrayList<Alunno> alunnoAL = null;
	
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet res = null;

		try {
			conn = (Connection) cp.getConnection();
			stat = (PreparedStatement) conn.prepareStatement(QueryConstant.SELECT_ALUNNI_DELLA_CLASSE);
			stat.setInt(1, id_classe);
		

			res = (ResultSet) stat.executeQuery();

			int i = 0;
			while (res.next()) {
				if(i==0){
					alunnoAL=new ArrayList<Alunno>();
					i++;
				}
				
				alunnoAL.add(new Alunno(res));
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (res != null) {
				try {
					res.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				res = null;
			}
			if (stat != null) {
				try {
					stat.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				stat = null;
			}
			if (conn != null) {
				cp.returnConnection(conn);
				conn = null;
			}
		}

		return alunnoAL;
		
		
	}

	
	
	

		
		
	

}
